Lookup Relationships

Overview

The Table Relationships Panel can also be used to define lookup relationships between tables.

Simple code-description lookups (e.g. product code to product description) are specified in Define Variables (see Decodes). In FastStats terms Lookup Tables are only required when multiple fields are required to be linked into the FastStats database.

Lookup relationships are often used as part of the flattening (de-normalisation) of database structures into the natural entities that are sensible for presentation to marketing analysts.

FastStats Designer supports various types of lookups. For example, it is possible to lookup the details of a product on a transaction record and retrieve the supplier, cost, margin etc. fields that can then be made available for analysis.

FastStats query performance benefits from the lookup because unlike a relational database, it does not need to make a join to another table to retrieve the lookup data, it is pre-retrieved at the time the data is loaded.

Define A Lookup Relationship

To create a lookup, right click on the relationship and choose Change to lookup relationship. Note that the infinity symbol is removed from the relationship line as the cardinality of the lookup relationship is no longer known (it could be that many records will lookup the same record, that the records will lookup one to one or that there are many matched records in the lookup table for each source record).

In this example the Destinations table has been defined as a lookup onto the Destination field in the Bookings table. The data from the lookup table can be considered as appended to the parent table (in this case Bookings). Each successful lookup will retrieve a whole lookup record (in this case a Destination). Unsuccessful lookups will return a blank record.